package DataLayer.DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DataLayer.Model.Produkt;

public class DBProdukt implements IFDBProdukt
{
	private Connection con = DBConnection.getInstance().getDBcon();

	@Override
	public ArrayList<Produkt> getAllProdukt(boolean synlig)
	{
		int s = (synlig ? 1 : 0);
		return miscWhere("synlig = '" + s + "'");
	}
	
	public ArrayList<Produkt> getAllProdukt()
	{
		return miscWhere("");
	}

	@Override
	public ArrayList<Produkt> searchProduktViaType(String type, boolean synlig)
	{
		String wClause = " type = '" + type + "'";
		return miscWhere(wClause);
	}

	@Override
	public ArrayList<Produkt> searchProduktViaModel(String model, boolean synlig)
	{
		String wClause = " model = '" + model + "'," +
				" synlig = '1'";
		return miscWhere(wClause);
	}

	@Override
	public Produkt getProdukt(int produktId)
	{
		String wClause = " produktId = '" + produktId + "'";
		return singleWhere(wClause);
	}

	@Override
	public int insertProdukt(Produkt p) throws Exception
	{
		int nextID = GetMax.getMaxId("SELECT MAX(produktId) FROM Produkt");
		nextID = nextID + 1;
		int rc = -1;
		String query = "INSERT INTO Produkt(produktId, type, model, directOnly, pris, synlig)  VALUES('"
				+
				nextID + "','" +
				p.getType() + "','" +
				p.getModel() + "','" +
				(p.isDirectOnly() ? 1 : 0) + "','" +
				p.getPris() + "','" +
				(p.isSynlig() ? 1 : 0) + "')";
		System.out.println("insert : " + query);
		try
		{ // insert new Produkt
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex)
		{
			ex.printStackTrace();
			System.out.println("Produkt was not inserted");
		}
		return (rc);
	}

	@Override
	public int updateProdukt(Produkt produkt)
	{
		Produkt p = produkt;
		int rc = -1;
		String query = "UPDATE Produkt SET " +
				"type ='" + p.getType() + "', " +
				"model ='" + p.getModel() + "', " +
				"directOnly ='" + (p.isDirectOnly() == true ? 1 : 0) + "', " +
				"pris ='" + p.getPris() + "', " +
				"synlig ='" + (p.isSynlig() == true ? 1 : 0) + "'" +
				" WHERE ProduktId = '" + p.getProduktId() + "'";
		System.out.println("Update query:" + query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex)
		{
			System.out.println("Update exception in Produkt db: " + ex);
		}
		return (rc);
	}

	@Override
	public int deleteProdukt(int produktId)
	{
		int rc = -1;
		String query = "DELETE FROM Produkt WHERE produktId = '" + produktId
				+ "'";
		System.out.println(query);
		try
		{ // delete FROM Produkt
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex)
		{
			System.out.println("Delete exception in Produkt db: " + ex);
		}
		return (rc);
	}

	private ArrayList<Produkt> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Produkt> list = new ArrayList<Produkt>();
		String query = buildQuery(wClause);
		query += " ORDER by type, model";
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				Produkt p = new Produkt();
				p = buildProdukt(results);
				list.add(p);
			}
			stmt.close();
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Produkt singleWhere(String wClause)
	{
		ResultSet results;
		Produkt p = new Produkt();
		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next())
			{
				p = buildProdukt(results);
				stmt.close();
			}
			else
			{
				p = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return p;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT produktId, type, model, directOnly, pris, synlig FROM Produkt ";
		if (wClause.length() > 0)
		{
			query += "WHERE " + wClause;
		}
		return query;
	}

	/**
	 * builds an object p of the class Produkt
	 * 
	 * @return Produkt
	 */
	private Produkt buildProdukt(ResultSet results)
	{
		Produkt p = new Produkt();
		try
		{
			p.setProduktId(results.getInt("produktId"));
			p.setType(results.getString("type"));
			p.setModel(results.getString("model"));
			p.setDirectOnly(results.getBoolean("directOnly"));
			p.setPris(results.getDouble("pris"));
			p.setSynlig(results.getBoolean("synlig"));
		}
		catch (Exception e)
		{
			System.out.println("error in building the Produkt object");
		}
		return p;
	}
}
